# 创建表
import xlrd

from PyMySQLUtils import PyMySQLUtils


def create_table():
    print("创建表开始...")
    utils = PyMySQLUtils()
    # 使用预处理语句创建表，若不存在则创建，若存在则跳过
    sql1 = """CREATE TABLE IF NOT EXISTS t_student_score(
                 sid int(11) NOT NULL  COMMENT '学号',
                 sname varchar(32) NOT NULL COMMENT '学生姓名',
                 sdept varchar(32) NOT NULL COMMENT '院系',
                 chinese float(5,2) NOT NULL COMMENT '语文成绩',
                 math float(5,2) NOT NULL  COMMENT '数学成绩',
                 english float(5,2) NOT NULL COMMENT '英语成绩',
                 average_score float(5,2) NOT NULL COMMENT '平均分' ,
                 classmate_score float(5,2) NOT NULL COMMENT '同学互评分',
                 teacher_score float(5,2) NOT NULL COMMENT '任课教师评分',
                 total_score float(5,2) NOT NULL COMMENT '综合测评总分',
                 PRIMARY KEY (`sid`)
                 ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT='学生成绩表'
              """
    utils.execute(sql1)
    # 使用预处理语句创建表，若不存在则创建，若存在则跳过
    sql2 = """CREATE TABLE IF NOT EXISTS t_teacher_login(
                 id int(11) NOT NULL AUTO_INCREMENT  COMMENT '主键id',
                 username varchar(32) NOT NULL COMMENT '用户名称',
                 password varchar(32) NOT NULL COMMENT '用户密码',
                 PRIMARY KEY (`id`)
                 ) ENGINE = InnoDB DEFAULT CHARSET = utf8  COMMENT='教师表'
              """
    utils.execute(sql2)
    print("创建表成功...")
    utils.close()


def excel_mysql():
    book = xlrd.open_workbook("StudentScore.xlsx")
    sheet = book.sheet_by_index(0)
    utils = PyMySQLUtils()
    for i in range(1, sheet.nrows):
        sid = sheet.cell(i, 0).value
        sname = sheet.cell(i, 1).value
        sdept = sheet.cell(i, 2).value
        chinese = sheet.cell(i, 3).value
        math = sheet.cell(i, 4).value
        english = sheet.cell(i, 5).value
        average_score = sheet.cell(i, 6).value
        classmate_score = sheet.cell(i, 7).value
        teacher_score = sheet.cell(i, 8).value
        total_score = sheet.cell(i, 9).value
        utils.execute(
            f"INSERT INTO t_student_score VALUES('{sid}', '{sname}', '{sdept}', {chinese}, {math}, {english}, "
            f"{average_score}, {classmate_score}, {teacher_score}, {total_score})")
    utils.close()

